package organizer.derby;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import organizer.DepthFirstAlgorithm;
import organizer.FileNodeReader;
import organizer.INodeVisitor;
import organizer.NodeVisitorAdapter;
import organizer.jdbc.JdbcUtil;

/**
 * Utility to configure a derby database.
 * 
 * @author dawong
 */
public final class DerbyEnvironment {

    private static final Logger log = Logger.getLogger(DerbyEnvironment.class);

    private static final String URL_PREFIX = "jdbc:derby:";
    private static final String DB_NAME = "organizer";
    public static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    public static final String USER = "root";
    public static final String PASSWORD = "";

    private static BasicDataSource dataSource;

    /** Singleton */
    private DerbyEnvironment() {
        
    }
    
    /**
     * Create the path for the database directory.  If the directory does not exist,
     * then create it.
     * @param id Differentiate between different versions of the database.
     * @return 
     */
    private static String dbPath(String id) {
        String homePath = System.getProperty("user.home");
        File homeDir = new File(homePath, ".organizer");
        File dbDir = new File(homeDir, "derby");
        if (!dbDir.exists()) {
            dbDir.mkdirs();
        }
        String dbPath = dbDir.getAbsolutePath();
        String dbName = DB_NAME + "_" + id;
        return new File(dbPath, dbName).getAbsolutePath();
    }
    
    /**
     * Create the url.
     * @param id Differentiate between different versions of the database.
     * @return 
     */
    public static String url(String id) {
        String url = URL_PREFIX + dbPath(id);
        return url;
    }

    /**
     * Create the datasource.
     * @param id Differentiate between different versions of the database.
     */
    public static void init(String id) {
        if (dataSource != null) {
            try {
                dataSource.close();
            } catch (SQLException ex) {
                log.debug(null, ex);
            }
        }
        dataSource = null;
        try {
            Class.forName(DRIVER);
        } catch (Exception e) {
            log.error(null, e);
        }
//        deleteDatabase();
        createDataSource(id);
        if (!doesDatabaseExist(id))
            createDatabase(id);
    }

    /**
     * Create the datasource
     * @param id Differentiate between different versions of the database.
     */
    private static void createDataSource(String id) {
        if (dataSource == null) {
            BasicDataSource ds = new BasicDataSource();
            ds.setUrl(url(id));
            ds.setUsername(USER);
            ds.setPassword(PASSWORD);
            ds.setDriverClassName(DRIVER);
            dataSource = ds;
        }
    }

    /**
     * Create the database tables.
     * @param id Differentiate between different versions of the database.
     */
    private static void createDatabase(String id) {
        log.debug("creating database");
        String url = url(id) + ";create=true";
        Connection c = null;
        try {
            c = DriverManager.getConnection(url, USER, PASSWORD);
        } catch (SQLException e) {
            log.error(null, e);
        } finally {
            if (c != null)
                try { c.close(); } catch (SQLException e) { log.error(null, e); }
        }
        try {
            JdbcUtil jdbc = JdbcUtil.instance(dataSource);
            String sql = "create table filemeta (path varchar(1024) primary key, "
                    + "filesize bigint, "
                    + "modified_date bigint, "
                    + "small_checksum varchar(255), "
                    + "checksum varchar(255), "
                    + "scanned smallint "
                    + ")";
            jdbc.exec(sql, null);
            sql = "create index smallsumidx on filemeta (small_checksum)";
            jdbc.exec(sql, null);
            sql = "create index sumidx on filemeta (checksum)";
            jdbc.exec(sql, null);
            sql = "create index sizeidx on filemeta (filesize)";
            jdbc.exec(sql, null);
        } catch (SQLException e) {
            log.error(null, e);
        }
    }

    /**
     * Get the datasource.
     * @param id Differentiate between different versions of the database.
     * @return 
     */
    public static DataSource getDataSource(String id) {
        if (dataSource == null) {
            init(id);
        }
        return dataSource;
    }

    /**
     * Delete the database.
     * @param id Differentiate between different versions of the database.
     */
    public static void deleteDatabase(String id) {
        log.debug("deleting database");
        File f = new File(dbPath(id));
        if (f.exists()) {
            INodeVisitor<File> visitor = new NodeVisitorAdapter<File>() {

                @Override
                public void post(File node) {
                    node.delete();
                }
            };
            DepthFirstAlgorithm<File> alg = new DepthFirstAlgorithm<File>(new FileNodeReader());
            alg.visit(f, visitor);
        }
    }

    /**
     * Whether database has been created.
     * @param id Differentiate between different versions of the database.
     * @return 
     */
    public static boolean doesDatabaseExist(String id) {
        return new File(dbPath(id)).exists();
    }

}
